VBA SQL

#VBA SQL| 来源: 网络整理| 查看: 265

WHERE Clause

The where clause is used with a relational statement to isolate the object element or rowLets you restrict the rows returnedIf you restrict rows based on the result of a group function you must have a GROUP BY as well as a HAVING clause

SELECT * FROM CUSTOMERS WHERE (Country = 'Finland' OR Country = 'Brazil')

SELECT * FROM CUSTOMERS WHERE CompanyName = 'Island Trading'

--When using SQL on text data, "alfred" is greater than "a" (like in a dictionary).

SELECT CompanyName ,ContactName FROM CUSTOMERS WHERE CompanyName > 'g'

Note: Text strings should always be enclosed in quotes. if one of your values is a text string containing blanks or special characters you must enclose the swhole string in quotes.Note: If you are comparing date values you must enclose the date constants in pound sign (#)

--This works in SQL Server despite Extension being a text column - doesn't work in Access

SELECT * FROM EMPLOYEES WHERE EMPLOYEES.Extension > 3000

--This works in both Access and SQL server

SELECT * FROM EMPLOYEES WHERE Extension = '428'

--This works in SQL Server (despite Extension being a text column) but not in Access

SELECT * FROM EMPLOYEES WHERE Extension = 428 OR Clause

Operator Precedence

When multiple operators are used in a WHERE clause the operator precedence determines the order in which the operations are performed.If two operators in an expression have the same operator precedence level then they are evaluated from left to right.Since parentheses have the highest precedence level these can be used to override the default operator precedence.The following tables are shown in the order of precedence.

Comparison Operators>Greater Than>=Greater Than or Equal To=EqualLess ThanLess Than or Equal ToNot EqualLogical Operators

Whenever you use both the AND and OR operators together always use parentheses to ensure that you get the correct results.

ANDRequires both expressions on either side of the AND operator to be true for data to be returnedORRequires at least one expression on either side of the OR operator to be true in order for data to be returned.NOTUsed to match any condition opposite of the one defined.??? OperatorsBETWEENUsed to determine whether a value of an expression falls within a specified range of valuesINUsed to match conditions in a list of expressionsLIKEUsed to match patterns in dataIS NULLUsed to determine if a field contains dataIS NOT NULLUsed to determine if a field does not contain data.Arithmetic Operators

These are used to perform mathematical calculations.These are listed in order of precedence.

-Negation^Exponentiation (not available in SQL Server ?)/Divide*Multiple%Modulus=Plus-Minus !=, ^=Test for inequality=Test for equality||String concatenation:=Assignment operatorString Operators

When using SQL on text data, "alfred" is greater than "a" (like in a dictionary).

【本文地址】

公司简介

联系我们